package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.*;

public class DBOffer implements IFDBOffer {
	private Connection connection;
	private DBOfferLabour dbOfferLabour;
	private DBOfferMaterial dbOfferMaterial;

	public DBOffer()
	{
		connection = DbConnection.getInstance().getDBcon();
		dbOfferLabour = new DBOfferLabour();
		dbOfferMaterial = new DBOfferMaterial();
	}
	
	
	
	public ArrayList<Offer> getAllOffers() 
	{
		return  miscWhere("");
	}

	
	
	
	public Offer findOffer(int offerId) {
		return singleWhere("offerId = "+offerId);
	}
	
	public Offer findOffer2(int offerId)
    {   String wClause = "  offerId = '" + offerId + "'";
        return singleWhere(wClause);
    }
	
	
	

	public int deleteOffer(int offerId)
	{
		int rc = -1;
		
		String query = "DELETE FROM Offer WHERE offerId = "+offerId;
		
		try{
			Statement stmt = connection.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			
			
			stmt.close();
		}
		catch(Exception ex){
			System.err.println("Couldn't delete the offer with offerId "+offerId);
		}
		
		
		return rc;
	}
	
	
	public int insertOffer(Offer offer) throws Exception 
	{
		int rc = -1;
		
		String query1 = "INSERT INTO Offer (offerId,customerId,date) VALUES ("+offer.getOfferId()+","+offer.getCustomerId()+",'"+offer.getOfferDate()+"')";

		
		try{
			Statement stmt = connection.createStatement();
			stmt.setQueryTimeout(5);
			
			rc = stmt.executeUpdate(query1, Statement.RETURN_GENERATED_KEYS);	
			
			ResultSet rs = stmt.getGeneratedKeys();
	        if (rs.next())  rc = rs.getInt(1);
	        rs.close();
	        
	        
			/*if(rc > 0)
			{
				for(OfferLabour offerLabour : offer.getOfferLabours())
				{
					offerLabour.setOfferId(rc);
					dbOfferLabour.insertOfferLabour(offerLabour);
				}
	
			}
			*/
			
			stmt.close();
		}
		catch(Exception ex){
			System.err.println("Couldn't insert the offer with offerId: "+offer.getOfferId()+" "+query1);
		}
		
		
		return rc;
	}

	
	public int updateOffer(Offer offer) 
	{
		int rc = -1;
		
		String query1 = "UPDATE Offer SET offerId = "+offer.getOfferId()+",customerId = "+offer.getCustomerId()+", date = '"+offer.getOfferDate()+"' WHERE offerId = "+offer.getOfferId();
		
		
		try{
			Statement stmt = connection.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query1);
			
			
			stmt.close();
		}
		catch(Exception ex){
			System.err.println("Couldn't update the Offer with OfferID  "+offer.getOfferId());
		}
		
		
		return rc;
	}
	
	
	
	
	
	
	
	
	private Offer singleWhere(String wClause)
	{
		Offer offer = new Offer();
		
		ResultSet results;
		String query = buildQuery(wClause);
		
		
		try{
			Statement stmt = connection.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next())
				offer = buildOffer(results);
			
			stmt.close();
		}
		catch(Exception ex){}
		
		return offer;
	}
	
	
	
	private ArrayList<Offer> miscWhere(String wClause)
	{
		ArrayList<Offer> offers = new ArrayList<>();
		
		ResultSet results;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = connection.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			
			while(results.next())
			{
				Offer offer = buildOffer(results);
				offers.add(offer);
			}
			
			stmt.close();
			
		}
		catch(Exception ex){}
		return offers;
	}
	
	
	
	
	
	
	private Offer buildOffer(ResultSet result)
	{
		Offer offers = new Offer();
		try{	
			offers.setOfferId(result.getInt("offerId"));
			offers.setCustomerId(result.getInt("customerId"));
			offers.setDate(result.getString("date"));
			
			
			ArrayList<OfferLabour> offerLabour = dbOfferLabour.getAllOfferLaboursConnectedToOffer(offers.getOfferId());
			offers.setLabourItem(offerLabour);
				
		}
		catch(Exception ex){ System.err.println("Unable to build a Sale ");} 
		
		return offers;
	}
	
	
	private String buildQuery(String wClause)
	{
		String query = "Select offerId, customerId, date FROM Offer";
		
		if(wClause.length() > 0) query=query+" WHERE "+ wClause;
		
		System.out.println(query);
		
		return query;
	}


}

